package org.posupe.curriculos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.cursos.Curso;
import org.posupe.cursos.IteratorCursos;
import org.posupe.cursos.IteratorCursosVector;
import org.posupe.instituicoesEnsino.InstituicaoEnsinoInexistenteException;
import org.posupe.instituicoesEnsino.RepositorioInstituicoesEnsinoBDR;
import org.posupe.util.RepositorioException;

public class RepositorioCurriculosCursosBDR {

	private static RepositorioCurriculosCursosBDR instance;
		
	private PersistenceMechanismRDBMS pm;
	
	private static String SQL_INSERT_RELATION = "INSERT INTO CURRICULO_CURSO(COD_CURRICULO, COD_CURSO) VALUES (?,?)";
	private static String SQL_DELETE_RELATION = "DELETE FROM CURRICULO_CURSO WHERE COD_CURRICULO = ? AND COD_CURSO = ?";
	private static String SQL_IS_RELATED = "SELECT * FROM CURRICULO_CURSO WHERE COD_CURRICULO = ? AND COD_CURSO = ?";
	private static String SQL_GET_CURSOS_RELATED = "SELECT * FROM CURSO WHERE CODIGO IN (SELECT COD_CURSO FROM CURRICULO_CURSO WHERE COD_CURRICULO = ?)";	
	private static String SQL_DELETE_ALL = "DELETE FROM CURRICULO_CURSO WHERE COD_CURRICULO = ?";
	
	private RepositorioCurriculosCursosBDR() throws RepositorioException {
		try {
			this.pm = PersistenceMechanismRDBMS.getInstance();
			this.pm.connect();
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} 
	}
	
	public static RepositorioCurriculosCursosBDR getInstance() throws RepositorioException {
		if (instance == null) {
			instance = new RepositorioCurriculosCursosBDR();
		}
		return instance;
	}
	
	public void associar(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		try {
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(SQL_INSERT_RELATION);
			try {
				statement.setInt(1, codigoCurriculo);
				statement.setInt(2, codigoCurso);
				if (statement.executeUpdate() == 0){
					throw new RepositorioException(new Throwable("Erro ao associar curriculo ao curso."));
				}	
			} finally {
				statement.close();
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}    
	}

	public void desassociar(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		try {
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(SQL_DELETE_RELATION);
			try {
				statement.setInt(1, codigoCurriculo);
				statement.setInt(2, codigoCurso);
				int quantidade = statement.executeUpdate();
				if (quantidade == 0){
					throw new RepositorioException(new Throwable("Erro ao desassociar curriculo ao curso."));
				}
			} finally  {
				statement.close();
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
	}
	
	public void desassociarTodosCursos(int codigoCurriculo) throws RepositorioException {
		try {
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(SQL_DELETE_ALL);
			try {
				statement.setInt(1, codigoCurriculo);
				statement.executeUpdate();
			}  finally {
				statement.close();
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
	}

	public boolean isAssociado(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		boolean resultado = false;
		try {
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(SQL_IS_RELATED);
			try {
				statement.setInt(1, codigoCurriculo);
				statement.setInt(2, codigoCurso);
				ResultSet resultset = statement.executeQuery();
				resultado = resultset.next();
			} finally {
				statement.close();
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		return resultado;
	}

	public IteratorCursos getCursosAssociados(int codigoCurriculo) throws RepositorioException {
		IteratorCursosVector iteratorCursos = null;
		Curso c = null;
		try {
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(SQL_GET_CURSOS_RELATED);
			try {
				statement.setInt(1, codigoCurriculo);
				ResultSet resultset = statement.executeQuery();
				iteratorCursos = new IteratorCursosVector();
	
				while (resultset.next()){
					c = new Curso(resultset.getInt("CODIGO"),
	        				  resultset.getString("NOME"),
	        				  resultset.getString("DESCRICAO"),
	        		          resultset.getString("NIVEL"),
	        		          RepositorioInstituicoesEnsinoBDR.getInstance().procurar(resultset.getInt("COD_INSTITUICAO")),
	        		          resultset.getInt("CARGA_HORARIA"));
		            iteratorCursos.add(c);
				}
			} finally {
				statement.close();
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} catch (InstituicaoEnsinoInexistenteException e) {
			throw new RepositorioException(e);
		}
		return iteratorCursos;
	}
	
	
}
